﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using MDL;

namespace DAL
{
    public class DAL_Vips
    {
        /// <summary>
        /// 查询会员信息
        /// </summary>
        /// <param name="name">根据姓名模糊查询</param>
        /// <returns>会员信息</returns>
        public static List<Vips> SelectVips(string name)
        {
            string sql = "select * from Vips v inner join VIPGrade g on v.GradeID=g.VGID where 1=1 ";
            List<SqlParameter> sprs = new List<SqlParameter>();
            if (!name.Equals(""))
            {
                sql += " and VipName like @name";
                sprs.Add(new SqlParameter("@name", "%" + name + "%"));
            }
            List<Vips> list = new List<Vips>();
            using (SqlDataReader dr = DBHelper.白道(sql, sprs.ToArray(), CommandType.Text))
            {
                while (dr.Read())
                {
                    Vips v = new Vips();
                    v.VipID = Convert.ToInt32(dr[0]);
                    v.VipName = dr[1].ToString();
                    v.VipSex = dr[2].ToString();
                    v.VipTel = dr[4].ToString();
                    v.VipStartDate = Convert.ToDateTime(dr[5]);
                    v.VipEndDate = Convert.ToDateTime(dr[6]);
                    v.VGName = dr[8].ToString();
                    v.VGDiscount = Convert.ToDouble(dr[9]);
                    list.Add(v);
                }
            }
            return list;
        }
        /// <summary>
        /// 增加会员信息
        /// </summary>
        /// <param name="v">会员信息</param>
        /// <returns>受影响的行数</returns>
        public static int AddVips(Vips v)
        {
            string sql = "insert into Vips values(@name,@sex,@GID,@tel,@time1,@time)";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@name",v.VipName),
                                  new SqlParameter("@sex",v.VipSex),
                                  new SqlParameter("@GID",v.VGID),
                                  new SqlParameter("@tel",v.VipTel),
                                  new SqlParameter("@time1",v.VipStartDate),
                                  new SqlParameter("@time",v.VipEndDate)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 修改会员信息
        /// </summary>
        /// <param name="v">会员信息</param>
        /// <returns>受影响的行数</returns>
        public static int UpdateVips(Vips v)
        {
            string sql = "update Vips set VipName=@name,VipSex=@sex,GradeID=@GID,VipTel=@tel,VipEndDate=@time where VipID=@ID";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@name",v.VipName),
                                  new SqlParameter("@sex",v.VipSex),
                                  new SqlParameter("@GID",v.VGID),
                                  new SqlParameter("@tel",v.VipTel),
                                  new SqlParameter("@ID",v.VipID),
                                  new SqlParameter("@time",v.VipEndDate)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 删除会员信息
        /// </summary>
        /// <param name="v">会员信息</param>
        /// <returns>受影响的行数</returns>
        public static int DeleteVips(int id)
        {
            string sql = "delete from Vips where VipID = @id";
            SqlParameter[] sprs = { new SqlParameter("@id", id) };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 根据姓名或编号查询会员信息
        /// </summary>
        /// <param name="name">会员姓名</param>
        /// <param name="id">会员id</param>
        /// <param name="b">根据哪个查询</param>
        /// <returns>会员信息</returns>
        public static Vips SelectInfo(string name, int id, bool b)
        {
            string sql = "select * from Vips a left join VIPGrade b on a.GradeID=b.VGID where VipEndDate >= GETDATE()";
            List<SqlParameter> sprs = new List<SqlParameter>();
            if (b)
            {
                sql += " and VipID = @id";
                sprs.Add(new SqlParameter("@id", id));
            }
            else
            {
                sql += " and VipName like @name";
                sprs.Add(new SqlParameter("@name", name));
            }
            using (SqlDataReader sdr = DBHelper.白道(sql, sprs.ToArray(), CommandType.Text))
            {
                if (sdr.Read())
                {
                    Vips vip = new Vips();
                    vip.VipID = Convert.ToInt32(sdr[0]);
                    vip.VipName = sdr[1].ToString();
                    vip.VipEndDate = Convert.ToDateTime(sdr[6]);
                    vip.VGName = sdr[8].ToString();
                    vip.VGDiscount = Convert.ToDouble(sdr[9]);
                    return vip;
                }
                else
                {
                    return null;
                }
            }
        }
    }
}
